package com.youhong.teethcare.utils;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.ListView;

import com.youhong.teethcare.javabeans.BrushingRecord;
import com.youhong.teethcare.javabeans.PlaqueRecord;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 * Created by Administrator on 2017/5/16.
 */
public class DBHelper extends SQLiteOpenHelper {

    private static DBHelper dbHelper = null;
    private static final String DB_NAME = "TeethCare";
    private static final int DB_VERSION = 3;
    private Context mContext = null;

    public static DBHelper getDbHelper(Context context) {

        if (dbHelper == null) {

            dbHelper = new DBHelper(context);
        }
        return dbHelper;
    }

    SQLiteDatabase db = null;

    public DBHelper(Context context) {
        // TODO Auto-generated constructor stub
        super(context, DB_NAME, null, DB_VERSION);
        this.mContext = context;
        db = super.getWritableDatabase();
    }

    private static final String ID = "_id";
    private static final String UID = "_uid";
    private static final String START_TIME = "_startTime";
    private static final String AREA1 = "_area1";
    private static final String AREA2 = "_area2";
    private static final String AREA3 = "_area3";
    private static final String AREA4 = "_area4";
    private static final String AREA5 = "_area5";
    private static final String AREA6 = "_area6";
    private static final String PERCENT = "_percent";
    private static final String TOTAL_TIME = "_totalTime";
    private static final String SURFACE_COUNT = "_surfaceCount";
    private static final String BrushMode = "brushMode";
    private static final String BrushLevel = "BrushLevel";


    //0:RealTime ,1: non-RealTime
    private static final String TYPE = "_type";

    @Override
    public void onCreate(SQLiteDatabase db) {

        String sql = "create table BrushingRecord(" +
                "_id integer primary key autoincrement," +
                "_uid varchar," +
                "_startTime datetime," +
                "_totalTime INTEGER," +
                "_area1 INTEGER," +
                "_area2 INTEGER," +
                "_area3 INTEGER," +
                "_area4 INTEGER," +
                "_area5 INTEGER," +
                "_area6 INTEGER," +
                "_percent INTEGER," +
                "brushMode INTEGER," +
                "BrushLevel INTEGER," +
                "_type INTEGER," +
                "_surfaceCount INTEGER" +
                ")";

        db.execSQL(sql);

        String sql2 = "create table PlaqueRecord(" +
                "_id integer primary key autoincrement," +
                "_uid varchar," +
                "_date datetime," +
                "_value1 varchar," +
                "_value2 varchar," +
                "_value3 varchar," +
                "_value4 varchar," +
                "_value5 varchar," +
                "_value6 varchar," +
                "_value7 varchar," +
                "_value8 varchar," +
                "_value9 varchar," +
                "_value10 varchar," +
                "_value11 varchar," +
                "_value12 varchar," +
                "_value13 varchar," +
                "_value14 varchar," +
                "_value15 varchar," +
                "_value16 varchar," +
                // type:0=Plaque,   type:1=toothMobility type,
                // type:2=Bleeding, type:3=Pocket depth
                "_value17 varchar," +
                "_value18 varchar," +
                "_value19 varchar," +
                "_value20 varchar," +
                "_value21 varchar," +
                "_value22 varchar," +
                "_value23 varchar," +
                "_value24 varchar," +
                "_value25 varchar," +
                "_value26 varchar," +
                "_value27 varchar," +
                "_value28 varchar," +
                "_value29 varchar," +
                "_value30 varchar," +
                "_value31 varchar," +
                "_value32 varchar," +
                "_type INTEGER" +
                ")";

        db.execSQL(sql2);
   /*     String sql2 = "create table goalRecord(_id integer primary key autoincrement,_uid varchar,_time datetime,_drinkGoalML INTEGER)";
        db.execSQL(sql2);*/
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion == 1) {
            db.execSQL("alter table BrushingRecord add brushMode int ");
            db.execSQL("alter table BrushingRecord add BrushLevel int ");
        } else if (oldVersion == 2) {
            db.execSQL("alter table BrushingRecord add BrushLevel int ");
        }

    }

    public List<PlaqueRecord> queryPlaqueRecordByDay(Calendar calendar) {

        String sql = "select * from PlaqueRecord where  cast(strftime('%Y',_date) as Integer) = ? " +
                "and cast(strftime('%m',_date) as Integer) = ?  and cast(strftime('%d',_date )as INTEGER) = ? order by _date desc";

        List<PlaqueRecord> list = new ArrayList<>();
        Cursor cursor = null;

        try {

            cursor = db.rawQuery(sql, new String[]{
                    calendar.get(Calendar.YEAR) + "",
                    calendar.get(Calendar.MONTH) + 1 + "",
                    calendar.get(Calendar.DAY_OF_MONTH) + ""
            });

            while (cursor.moveToNext()) {

                PlaqueRecord record = new PlaqueRecord();

                record.setId(cursor.getInt(cursor.getColumnIndex(ID)));
                record.setUid(cursor.getString(cursor.getColumnIndex(UID)));
                record.setDate(Util.analyzeDateString(cursor.getString(cursor.getColumnIndex("_date"))));
                record.setValue1(cursor.getString(cursor.getColumnIndex("_value1")));
                record.setValue2(cursor.getString(cursor.getColumnIndex("_value2")));
                record.setValue3(cursor.getString(cursor.getColumnIndex("_value3")));
                record.setValue4(cursor.getString(cursor.getColumnIndex("_value4")));
                record.setValue5(cursor.getString(cursor.getColumnIndex("_value5")));
                record.setValue6(cursor.getString(cursor.getColumnIndex("_value6")));
                record.setValue7(cursor.getString(cursor.getColumnIndex("_value7")));
                record.setValue8(cursor.getString(cursor.getColumnIndex("_value8")));
                record.setValue9(cursor.getString(cursor.getColumnIndex("_value9")));
                record.setValue10(cursor.getString(cursor.getColumnIndex("_value10")));
                record.setValue11(cursor.getString(cursor.getColumnIndex("_value11")));
                record.setValue12(cursor.getString(cursor.getColumnIndex("_value12")));
                record.setValue13(cursor.getString(cursor.getColumnIndex("_value13")));
                record.setValue14(cursor.getString(cursor.getColumnIndex("_value14")));
                record.setValue15(cursor.getString(cursor.getColumnIndex("_value15")));
                record.setValue16(cursor.getString(cursor.getColumnIndex("_value16")));
                record.setValue17(cursor.getString(cursor.getColumnIndex("_value17")));
                record.setValue18(cursor.getString(cursor.getColumnIndex("_value18")));
                record.setValue19(cursor.getString(cursor.getColumnIndex("_value19")));
                record.setValue20(cursor.getString(cursor.getColumnIndex("_value20")));
                record.setValue21(cursor.getString(cursor.getColumnIndex("_value21")));
                record.setValue22(cursor.getString(cursor.getColumnIndex("_value22")));
                record.setValue23(cursor.getString(cursor.getColumnIndex("_value23")));
                record.setValue24(cursor.getString(cursor.getColumnIndex("_value24")));
                record.setValue25(cursor.getString(cursor.getColumnIndex("_value25")));
                record.setValue26(cursor.getString(cursor.getColumnIndex("_value26")));
                record.setValue27(cursor.getString(cursor.getColumnIndex("_value27")));
                record.setValue28(cursor.getString(cursor.getColumnIndex("_value28")));
                record.setValue29(cursor.getString(cursor.getColumnIndex("_value29")));
                record.setValue30(cursor.getString(cursor.getColumnIndex("_value30")));
                record.setValue31(cursor.getString(cursor.getColumnIndex("_value31")));
                record.setValue32(cursor.getString(cursor.getColumnIndex("_value32")));


                record.setType(cursor.getInt(cursor.getColumnIndex(TYPE)));
                list.add(record);
            }
        } catch (Exception exp) {

            exp.printStackTrace();

        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return list;
    }

    public boolean deletePlaqueRecordByDay(Calendar calendar) {
        String sql = "Delete from PlaqueRecord where  cast(strftime('%Y',_date) as Integer) = ? " +
                "and cast(strftime('%m',_date) as Integer) = ?  and cast(strftime('%d',_date ) as Integer) = ?";
        try {

            db.execSQL(sql, new Object[]{
                    calendar.get(Calendar.YEAR) + "",
                    calendar.get(Calendar.MONTH) + 1 + "",
                    calendar.get(Calendar.DAY_OF_MONTH) + ""
            });
            return true;
        } catch (Exception exp) {
            exp.printStackTrace();
        }
        return false;
    }

    public boolean insertPlaqueRecord(List<PlaqueRecord> list) {

        for (int i = 0; i < list.size(); i++) {

            String sql = "insert into PlaqueRecord values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            try {
                PlaqueRecord record = list.get(i);
                db.execSQL(sql, new Object[]{record.getUid(),
                                Util.FormDateTimeString(record.getDate()),
                                record.getValue1(),
                                record.getValue2(),
                                record.getValue3(),
                                record.getValue4(),
                                record.getValue5(),
                                record.getValue6(),
                                record.getValue7(),
                                record.getValue8(),
                                record.getValue9(),
                                record.getValue10(),
                                record.getValue11(),
                                record.getValue12(),
                                record.getValue13(),
                                record.getValue14(),
                                record.getValue15(),
                                record.getValue16(),
                                record.getValue17(),
                                record.getValue18(),
                                record.getValue19(),
                                record.getValue20(),
                                record.getValue21(),
                                record.getValue22(),
                                record.getValue23(),
                                record.getValue24(),
                                record.getValue25(),
                                record.getValue26(),
                                record.getValue27(),
                                record.getValue28(),
                                record.getValue29(),
                                record.getValue30(),
                                record.getValue31(),
                                record.getValue32(),
                                record.getType(),
                        }
                );
            } catch (Exception ex) {

                ex.printStackTrace();
            }
        }
        return true;
    }

    /**
     * DrinkRecord
     **/
    public List<BrushingRecord> queryBrushRecordByDay(Calendar calendar, int type) {
        String sql = null;
        if (type == 1) {

            sql = "select * from BrushingRecord where  cast(strftime('%Y',_startTime) as Integer) = ? " +
                    "and cast(strftime('%m',_startTime) as Integer) = ?  and cast(strftime('%d',_startTime )as INTEGER) = ? order by _startTime desc ";

        } else {

            sql = "select * from BrushingRecord where  cast(strftime('%Y',_startTime) as Integer) = ? " +
                    "and cast(strftime('%m',_startTime) as Integer) = ?  and cast(strftime('%d',_startTime )as INTEGER) = ? and _type = 0 order by _startTime desc ";
        }

        List<BrushingRecord> list = new ArrayList<>();
        Cursor cursor = null;

        try {

            cursor = db.rawQuery(sql, new String[]{
                    calendar.get(Calendar.YEAR) + "",
                    calendar.get(Calendar.MONTH) + 1 + "",
                    calendar.get(Calendar.DAY_OF_MONTH) + ""
            });


            while (cursor.moveToNext()) {

                BrushingRecord record = new BrushingRecord();

                record.setId(cursor.getInt(cursor.getColumnIndex(ID)));
                record.setUid(cursor.getString(cursor.getColumnIndex(UID)));
                record.setStartTime(Util.analyzeDateString(cursor.getString(cursor.getColumnIndex(START_TIME))));

                record.setTotalTime(cursor.getInt(cursor.getColumnIndex(TOTAL_TIME)));
                record.setArea1(cursor.getInt(cursor.getColumnIndex(AREA1)));
                record.setArea2(cursor.getInt(cursor.getColumnIndex(AREA2)));
                record.setArea3(cursor.getInt(cursor.getColumnIndex(AREA3)));
                record.setArea4(cursor.getInt(cursor.getColumnIndex(AREA4)));
                record.setArea5(cursor.getInt(cursor.getColumnIndex(AREA5)));
                record.setArea6(cursor.getInt(cursor.getColumnIndex(AREA6)));
                record.setPercent(cursor.getInt(cursor.getColumnIndex(PERCENT)));
                record.setType(cursor.getInt(cursor.getColumnIndex(TYPE)));
                record.setSurfaceCount(cursor.getInt(cursor.getColumnIndex(SURFACE_COUNT)));
                record.setBrushMode(cursor.getInt(cursor.getColumnIndex(BrushMode)));
                record.setBrushLevel(cursor.getInt(cursor.getColumnIndex(BrushLevel)));


                list.add(record);
            }
        } catch (Exception exp) {

            exp.printStackTrace();

        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return list;
    }

    public List<BrushingRecord> queryBrushRecordByWeek(int year, int week, int type) {

        String sql;
        if (type == 1) {

            sql = "select * from BrushingRecord where  cast(strftime('%W',_startTime) as INTEGER) = ? " +
                    "and cast(strftime('%Y',_startTime) as INTEGER) = ? order by _startTime desc";

        } else {

            sql = "select * from BrushingRecord where  cast(strftime('%W',_startTime) as INTEGER) = ? " +
                    "and cast(strftime('%Y',_startTime) as INTEGER) = ? and _type = 0 order by _startTime desc";


        }
        List<BrushingRecord> list = new ArrayList<>();
        Cursor cursor = null;
        try {

            cursor = db.rawQuery(sql, new String[]{week + "", year + ""});

            while (cursor.moveToNext()) {

                BrushingRecord record = new BrushingRecord();

                record.setId(cursor.getInt(cursor.getColumnIndex(ID)));
                record.setUid(cursor.getString(cursor.getColumnIndex(UID)));
                record.setStartTime(Util.analyzeDateString(cursor.getString(cursor.getColumnIndex(START_TIME))));

                record.setTotalTime(cursor.getInt(cursor.getColumnIndex(TOTAL_TIME)));
                record.setArea1(cursor.getInt(cursor.getColumnIndex(AREA1)));
                record.setArea2(cursor.getInt(cursor.getColumnIndex(AREA2)));
                record.setArea3(cursor.getInt(cursor.getColumnIndex(AREA3)));
                record.setArea4(cursor.getInt(cursor.getColumnIndex(AREA4)));
                record.setArea5(cursor.getInt(cursor.getColumnIndex(AREA5)));
                record.setArea6(cursor.getInt(cursor.getColumnIndex(AREA6)));
                record.setPercent(cursor.getInt(cursor.getColumnIndex(PERCENT)));
                record.setType(cursor.getInt(cursor.getColumnIndex(TYPE)));
                record.setSurfaceCount(cursor.getInt(cursor.getColumnIndex(SURFACE_COUNT)));
                record.setBrushMode(cursor.getInt(cursor.getColumnIndex(BrushMode)));
                record.setBrushLevel(cursor.getInt(cursor.getColumnIndex(BrushLevel)));


                list.add(record);

            }


        } catch (Exception exp) {


        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return list;

    }

    public List<BrushingRecord> queryBrushRecordGroupByMonth(int month, int year, int type) {

        String sql;
        if (type == 1) {

            sql = "select MAX(_startTime),AVG(_totalTime),COUNT(_totalTime),AVG(_percent) from BrushingRecord where cast(strftime('%m',_startTime) as INTEGER) = ? " +
                    "and cast(strftime('%Y',_startTime) as INTEGER) = ?  group by strftime('%Y-%m-%d',_startTime)";
        } else {
            sql = "select MAX(_startTime),AVG(_totalTime),COUNT(_totalTime),AVG(_percent) from BrushingRecord where cast(strftime('%m',_startTime) as INTEGER) = ? " +
                    "and cast(strftime('%Y',_startTime) as INTEGER) = ? and _type = 0 group by strftime('%Y-%m-%d',_startTime)";

        }

        List<BrushingRecord> list = new ArrayList<>();
        Cursor cursor = db.rawQuery(sql, new String[]{month + 1 + "", year + ""});

        while (cursor.moveToNext()) {

            BrushingRecord record = new BrushingRecord();

        /*    record.setId(cursor.getInt(cursor.getColumnIndex(ID)));
            record.setUid(cursor.getString(cursor.getColumnIndex(UID)));*/

            record.setStartTime(Util.analyzeDateString(cursor.getString(0)));
            record.setTotalTime(cursor.getInt(1));
            record.setCount(cursor.getInt(2));
            record.setPercent(cursor.getInt(3));

        /*    record.setArea1(cursor.getInt(cursor.getColumnIndex(AREA1)));
            record.setArea2(cursor.getInt(cursor.getColumnIndex(AREA2)));
            record.setArea3(cursor.getInt(cursor.getColumnIndex(AREA3)));
            record.setArea4(cursor.getInt(cursor.getColumnIndex(AREA4)));
            record.setArea5(cursor.getInt(cursor.getColumnIndex(AREA5)));
            record.setArea6(cursor.getInt(cursor.getColumnIndex(AREA6)));
            record.setPercent(cursor.getInt(cursor.getColumnIndex(PERCENT)));
            record.setType(cursor.getInt(cursor.getColumnIndex(TYPE)));
            record.setSurfaceCount(cursor.getInt(cursor.getColumnIndex(SURFACE_COUNT)));
*/
            list.add(record);

        }

        return list;
    }

    public List<BrushingRecord> queryBrushRecordGroupByYear(int year, int type) {

        String sql;
        if (type == 1) {

            sql = "select MAX(_startTime),AVG(_totalTime),COUNT(_totalTime),AVG(_percent) from BrushingRecord where " +
                    "cast(strftime('%Y',_startTime) as INTEGER) = ?  group by strftime('%Y-%m',_startTime)";
        } else {
            sql = "select MAX(_startTime),AVG(_totalTime),COUNT(_totalTime),AVG(_percent) from BrushingRecord where  " +
                    "cast(strftime('%Y',_startTime) as INTEGER) = ? and _type = 0 group by strftime('%Y-%m',_startTime)";

        }

        List<BrushingRecord> list = new ArrayList<>();
        Cursor cursor = db.rawQuery(sql, new String[]{year + ""});

        while (cursor.moveToNext()) {

            BrushingRecord record = new BrushingRecord();

        /*    record.setId(cursor.getInt(cursor.getColumnIndex(ID)));
            record.setUid(cursor.getString(cursor.getColumnIndex(UID)));*/

            record.setStartTime(Util.analyzeDateString(cursor.getString(0)));
            record.setTotalTime(cursor.getInt(1));
            record.setCount(cursor.getInt(2));
            record.setPercent(cursor.getInt(3));

        /*    record.setArea1(cursor.getInt(cursor.getColumnIndex(AREA1)));
            record.setArea2(cursor.getInt(cursor.getColumnIndex(AREA2)));
            record.setArea3(cursor.getInt(cursor.getColumnIndex(AREA3)));
            record.setArea4(cursor.getInt(cursor.getColumnIndex(AREA4)));
            record.setArea5(cursor.getInt(cursor.getColumnIndex(AREA5)));
            record.setArea6(cursor.getInt(cursor.getColumnIndex(AREA6)));
            record.setPercent(cursor.getInt(cursor.getColumnIndex(PERCENT)));
            record.setType(cursor.getInt(cursor.getColumnIndex(TYPE)));
            record.setSurfaceCount(cursor.getInt(cursor.getColumnIndex(SURFACE_COUNT)));
*/
            list.add(record);
        }
        return list;
    }

    public List<BrushingRecord> queryBrushRecordGroupByYearDay(int year, int type) {

        String sql;
        if (type == 1) {

            sql = "select MAX(_startTime),AVG(_totalTime),COUNT(_totalTime),AVG(_percent) from BrushingRecord where " +
                    "cast(strftime('%Y',_startTime) as INTEGER) = ? group by strftime('%Y-%m-%d',_startTime)";

        } else {

            sql = "select MAX(_startTime),AVG(_totalTime),COUNT(_totalTime),AVG(_percent) from BrushingRecord where " +
                    "cast(strftime('%Y',_startTime) as INTEGER) = ? and _type = 0 group by strftime('%Y-%m-%d',_startTime)";
        }

        List<BrushingRecord> list = new ArrayList<>();
        Cursor cursor = db.rawQuery(sql, new String[]{year + ""});

        while (cursor.moveToNext()) {

            BrushingRecord record = new BrushingRecord();

        /*    record.setId(cursor.getInt(cursor.getColumnIndex(ID)));
            record.setUid(cursor.getString(cursor.getColumnIndex(UID)));*/

            record.setStartTime(Util.analyzeDateString(cursor.getString(0)));
            record.setTotalTime(cursor.getInt(1));
            record.setCount(cursor.getInt(2));
            record.setPercent(cursor.getInt(3));

        /*    record.setArea1(cursor.getInt(cursor.getColumnIndex(AREA1)));
            record.setArea2(cursor.getInt(cursor.getColumnIndex(AREA2)));
            record.setArea3(cursor.getInt(cursor.getColumnIndex(AREA3)));
            record.setArea4(cursor.getInt(cursor.getColumnIndex(AREA4)));
            record.setArea5(cursor.getInt(cursor.getColumnIndex(AREA5)));
            record.setArea6(cursor.getInt(cursor.getColumnIndex(AREA6)));
            record.setPercent(cursor.getInt(cursor.getColumnIndex(PERCENT)));
            record.setType(cursor.getInt(cursor.getColumnIndex(TYPE)));
            record.setSurfaceCount(cursor.getInt(cursor.getColumnIndex(SURFACE_COUNT)));
*/
            list.add(record);


        }


        return list;
    }

    public List<BrushingRecord> queryBrushRecordAfterTime(Calendar c) {

        String abc = Util.FormDateTimeString(c);

        String sql = "select * from BrushingRecord  where " +
                "_startTime >= ? order by _startTime desc";

        List<BrushingRecord> list = new ArrayList<>();
        Cursor cursor = null;
        try {

            cursor = db.rawQuery(sql, new String[]{
                    Util.FormDateTimeString(c)
            });

            while (cursor.moveToNext()) {

                BrushingRecord record = new BrushingRecord();

                record.setId(cursor.getInt(cursor.getColumnIndex(ID)));
                record.setUid(cursor.getString(cursor.getColumnIndex(UID)));
                record.setStartTime(Util.analyzeDateString(cursor.getString(cursor.getColumnIndex(START_TIME))));

                record.setTotalTime(cursor.getInt(cursor.getColumnIndex(TOTAL_TIME)));
                record.setArea1(cursor.getInt(cursor.getColumnIndex(AREA1)));
                record.setArea2(cursor.getInt(cursor.getColumnIndex(AREA2)));
                record.setArea3(cursor.getInt(cursor.getColumnIndex(AREA3)));
                record.setArea4(cursor.getInt(cursor.getColumnIndex(AREA4)));
                record.setArea5(cursor.getInt(cursor.getColumnIndex(AREA5)));
                record.setArea6(cursor.getInt(cursor.getColumnIndex(AREA6)));
                record.setPercent(cursor.getInt(cursor.getColumnIndex(PERCENT)));
                record.setType(cursor.getInt(cursor.getColumnIndex(TYPE)));
                record.setSurfaceCount(cursor.getInt(cursor.getColumnIndex(SURFACE_COUNT)));

                float val = cursor.getFloat(0);

                list.add(record);
            }
        } catch (Exception exp) {

            exp.printStackTrace();

        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return list;
    }

    public List<BrushingRecord> queryBrushRecordByMonth(int month, int year, int type) {

        String sql;
        if (type == 1) {

            sql = "select * from BrushingRecord where cast(strftime('%m',_startTime) as INTEGER) = ? " +
                    "and cast(strftime('%Y',_startTime) as INTEGER) = ? ";
        } else {
            sql = "select * from BrushingRecord where cast(strftime('%m',_startTime) as INTEGER) = ? " +
                    "and cast(strftime('%Y',_startTime) as INTEGER) = ? and _type = 0 ";
        }

        List<BrushingRecord> list = new ArrayList<>();
        Cursor cursor = db.rawQuery(sql, new String[]{month + 1 + "", year + ""});

        while (cursor.moveToNext()) {

            BrushingRecord record = new BrushingRecord();

            record.setId(cursor.getInt(cursor.getColumnIndex(ID)));
            record.setUid(cursor.getString(cursor.getColumnIndex(UID)));

            record.setStartTime(Util.analyzeDateString(cursor.getString(cursor.getColumnIndex(START_TIME))));
            record.setTotalTime(cursor.getInt(cursor.getColumnIndex(TOTAL_TIME)));
            record.setCount(cursor.getInt(2));
            record.setPercent(cursor.getInt(3));
            record.setArea1(cursor.getInt(cursor.getColumnIndex(AREA1)));
            record.setArea2(cursor.getInt(cursor.getColumnIndex(AREA2)));
            record.setArea3(cursor.getInt(cursor.getColumnIndex(AREA3)));
            record.setArea4(cursor.getInt(cursor.getColumnIndex(AREA4)));
            record.setArea5(cursor.getInt(cursor.getColumnIndex(AREA5)));
            record.setArea6(cursor.getInt(cursor.getColumnIndex(AREA6)));
            record.setPercent(cursor.getInt(cursor.getColumnIndex(PERCENT)));
            record.setType(cursor.getInt(cursor.getColumnIndex(TYPE)));
            record.setSurfaceCount(cursor.getInt(cursor.getColumnIndex(SURFACE_COUNT)));
            list.add(record);
        }

        return list;
    }

    public List<BrushingRecord> queryBrushRecordByYear(int year, int type) {

        String sql;
        if (type == 1) {

            sql = "select * from BrushingRecord where " +
                    "cast(strftime('%Y',_startTime) as INTEGER) = ?  ";
        } else {
            sql = "select * from BrushingRecord where  " +
                    "cast(strftime('%Y',_startTime) as INTEGER) = ? and _type = 0 ";

        }

        List<BrushingRecord> list = new ArrayList<>();
        Cursor cursor = db.rawQuery(sql, new String[]{year + ""});

        while (cursor.moveToNext()) {

            BrushingRecord record = new BrushingRecord();

            record.setId(cursor.getInt(cursor.getColumnIndex(ID)));
            record.setUid(cursor.getString(cursor.getColumnIndex(UID)));

            record.setStartTime(Util.analyzeDateString(cursor.getString(cursor.getColumnIndex(START_TIME))));
            record.setTotalTime(cursor.getInt(cursor.getColumnIndex(TOTAL_TIME)));
            record.setCount(cursor.getInt(2));
            record.setPercent(cursor.getInt(3));
            record.setArea1(cursor.getInt(cursor.getColumnIndex(AREA1)));
            record.setArea2(cursor.getInt(cursor.getColumnIndex(AREA2)));
            record.setArea3(cursor.getInt(cursor.getColumnIndex(AREA3)));
            record.setArea4(cursor.getInt(cursor.getColumnIndex(AREA4)));
            record.setArea5(cursor.getInt(cursor.getColumnIndex(AREA5)));
            record.setArea6(cursor.getInt(cursor.getColumnIndex(AREA6)));
            record.setPercent(cursor.getInt(cursor.getColumnIndex(PERCENT)));
            record.setType(cursor.getInt(cursor.getColumnIndex(TYPE)));
            record.setSurfaceCount(cursor.getInt(cursor.getColumnIndex(SURFACE_COUNT)));
            list.add(record);

        }
        return list;
    }

    private static final String TAG = "DBHelper";

    public boolean isExists(BrushingRecord record) {
        boolean isExists = false;
        String sql = "select * from  BrushingRecord where  _uid = ? " +
                "and _startTime = ?";
        Cursor cusor = db.rawQuery(sql, new String[]{record.getUid(), Util.FormDateTimeString(record.getStartTime())});
        if (cusor.getCount() != 0) isExists = true;
        cusor.close();
        return isExists;
    }

    public void insertBrushRecord(BrushingRecord record) {
        //如果存在相同数据，返回（不更新）
        boolean isExists = isExists(record);
         if(isExists)return;
        String sql = "insert into BrushingRecord values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        try {

            db.execSQL(sql, new Object[]{record.getUid(),
                            Util.FormDateTimeString(record.getStartTime()),
                            record.getTotalTime(),
                            record.getArea1(),
                            record.getArea2(),
                            record.getArea3(),
                            record.getArea4(),
                            record.getArea5(),
                            record.getArea6(),
                            record.getPercent(),
                            record.getBrushMode(),
                            record.getBrushLevel(),
                            record.getType(),
                            record.getSurfaceCount()


                    }
            );
        } catch (Exception exp) {

            exp.printStackTrace();

        }
    }

/*
    public List<BrushingRecord> queryBrushReocords() {

        String sql = "select * from BrushingRecord";

        List<BrushingRecord> list = new ArrayList<>();
        Cursor cursor = db.rawQuery(sql, new String[]{});

        while (cursor.moveToNext()) {

            BrushingRecord record = new BrushingRecord();

            record.setId(cursor.getInt(cursor.getColumnIndex(ID)));
            record.setUid(cursor.getString(cursor.getColumnIndex(UID)));
            record.setStartTime(Util.analyzeDateString(cursor.getString(cursor.getColumnIndex(START_TIME))));

            record.setTotalTime(cursor.getInt(cursor.getColumnIndex(TOTAL_TIME)));
            record.setArea1(cursor.getInt(cursor.getColumnIndex(AREA1)));
            record.setArea2(cursor.getInt(cursor.getColumnIndex(AREA2)));
            record.setArea3(cursor.getInt(cursor.getColumnIndex(AREA3)));
            record.setArea4(cursor.getInt(cursor.getColumnIndex(AREA4)));
            record.setArea5(cursor.getInt(cursor.getColumnIndex(AREA5)));
            record.setArea6(cursor.getInt(cursor.getColumnIndex(AREA6)));
            record.setPercent(cursor.getInt(cursor.getColumnIndex(PERCENT)));
            record.setType(cursor.getInt(cursor.getColumnIndex(TYPE)));

            list.add(record);

        }

        return list;
    }
*/

    public void deleteBrushRecords() {

        String sql = "Delete from BrushingRecord";
        try {

            db.execSQL(sql, new Object[]{});

        } catch (Exception exp) {
            exp.printStackTrace();
        }
    }

}